﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using MemoLib.Tasks;

namespace MemoLib
{
    public class ExcelOperator : IDisposable
    {
        public ExcelOperator(string pFilePath)
        {
            connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + pFilePath + ";"
                + "Extended ProPerties= \"Excel 12.0;HDR=Yes;\"";

            conn = new OleDbConnection(connStr);
            OpenConn();
            this.sheetTableLst = new Dictionary<string, DataTable>();
        }

        OleDbConnection conn = null;
        private string connStr = string.Empty;

        public void OpenConn()
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();
        }

        public void CloseConn()
        {
            if (this.conn != null && this.conn.State == System.Data.ConnectionState.Open)
            {
                this.conn.Close();
            }

            GC.Collect();
        }

        private Dictionary<string, DataTable> sheetTableLst = null;
        /// <summary>
        /// 将指定的Sheet数据转换为DataTable
        /// </summary>
        /// <param name="pSheetName"></param>
        /// <returns></returns>
        public DataTable GetTableFromExcelSheet(string pSheetName)
        {
            // 如果已经存在直接返回DataTable
            //if (sheetTableLst.Keys.Contains(pSheetName))
            //{
            //    return sheetTableLst[pSheetName];
            //}

            DataTable ret = null;
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            string strExcel = string.Empty;

            switch (pSheetName)
            { 
                case CommonData.ONCETASK_SHEETNAME:
                    strExcel = string.Format("select [{1}],[{2}],[{3}],[{4}],[{5}],[{6}], {8} from [{0}$] where [{7}] IS NULL", pSheetName, CommonData.ONCETASK_ID, CommonData.ONCETASK_CREATEDATE,
                    CommonData.ONCETASK_TYPE, CommonData.ONCETASK_CONTEXT, CommonData.ONCETASK_EXECUTEDATE, CommonData.ONCETASK_NOTE, CommonData.ONCETASK_STATUS,
                    CommonData.LASTEXECUTEDATETIME);
                    break;
                case CommonData.CYCLETASK_SHEETNAME:
                    strExcel = string.Format("select * from [{0}$]  Where [Status] IS NULL Order by {1},{2},{3},{4},{5},{6}", 
                        pSheetName, CommonData.CYCLETASK_FREQUENCY, CommonData.CYCLETASK_WEEKDAY, CommonData.CYCLETASK_MONTH, CommonData.CYCLETASK_DAY, CommonData.CYCLETASK_HOUR, CommonData.CYCLETASK_MINUTE);
                    break;
                case CommonData.TODOTASK_SHEETNAME:
                    strExcel = string.Format("select * from [{0}$] Where [Status] IS NULL Order By {1} DESC", pSheetName, CommonData.TODOTASK_REMINDDATETIME);
                    break;
            }
            this.OpenConn();
            myCommand = new OleDbDataAdapter(strExcel, this.conn);
            ds = new DataSet();
            myCommand.Fill(ds, pSheetName);
            if (ds != null && ds.Tables.Count > 0)
            {
                ret = ds.Tables[0];
                //this.sheetTableLst.Add(pSheetName, ret);
            }

            return ret;
        }

        /// <summary>
        /// 获取Excel单元格里面的值
        /// </summary>
        /// <param name="pSheetName"></param>
        /// <param name="pCellPosition"></param>
        /// <returns></returns>
        public object GetCellValue(string pSheetName, string pCellPosition)
        {
            object ret = null;
            DataTable dt = this.GetTableFromExcelSheet(pSheetName);
            if (dt == null)
                return ret;
            // eg.将“A30”转化为行30，列1
            string cellPosition = pCellPosition.ToUpper();
            int columnNum = Convert.ToInt32(cellPosition[0]) - 65;
            // Title行作为表头被去掉-1；索引从0开始-1
            int rowNum = Convert.ToInt32(cellPosition.Substring(1)) - 2;
            // 获取因子值
            ret = dt.Rows[rowNum][columnNum];

            return ret;
        }

        public void Dispose()
        {
            CloseConn();
        }

        public void UpdateCellValue(string pSheetName, string pFieldName, string pFieldValue, string pID)
        {
            OleDbCommand myCommand = null;
            string sql = string.Format("Update [{0}$] Set {1} = '{2}' where ID='{3}'", pSheetName, pFieldName, pFieldValue, pID);
            myCommand = new OleDbCommand(sql, this.conn);
            myCommand.ExecuteNonQuery();
        }
        /// <summary>
        /// Delete Row Data
        /// </summary>
        /// <param name="pSheetName"></param>
        /// <param name="pID"></param>
        public void DeleteRow(string pSheetName, string pID)
        {
            UpdateCellValue(pSheetName, "Status", "Delete", pID);
        }

        public void UpdateCycleTaskRowValue(CycleTaskCard pCard)
        {
            OleDbCommand myCommand = null;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("Update [{0}$] Set ", CommonData.CYCLETASK_SHEETNAME);
            sb.AppendFormat("{0} = '{1}'", CommonData.CYCLETASK_TYPE, pCard.ExecuteType);
            sb.AppendFormat(",{0} = '{1}'", CommonData.CYCLETASK_FREQUENCY, pCard.Frequency.ToString());
            sb.AppendFormat(",{0} = {1}", CommonData.CYCLETASK_QUANTITY, pCard.Quantity);
            if (pCard.Month.HasValue)
                sb.AppendFormat(",{0} = {1}", CommonData.CYCLETASK_MONTH, pCard.Month);
            if (pCard.Weekday.HasValue)
                sb.AppendFormat(",{0} = {1}", CommonData.CYCLETASK_WEEKDAY, pCard.Weekday);
            if (pCard.Day.HasValue)
                sb.AppendFormat(",{0} = {1}", CommonData.CYCLETASK_DAY, pCard.Day);
            if (pCard.Hour.HasValue)
                sb.AppendFormat(",{0} = {1}", CommonData.CYCLETASK_HOUR, pCard.Hour);
            if (pCard.Minute.HasValue)
                sb.AppendFormat(",{0} = {1}", CommonData.CYCLETASK_MINUTE, pCard.Minute);
            sb.AppendFormat(",{0} = '{1}'", CommonData.CYCLETASK_CONTEXT, pCard.Context);
            sb.AppendFormat(",{0} = '{1}'", CommonData.CYCLETASK_IFONLYWORKDAY, pCard.IfOnlyWorkday.ToString());
            sb.AppendFormat(" Where ID='{0}'", pCard.CardID);

            myCommand = new OleDbCommand(sb.ToString(), this.conn);
            myCommand.ExecuteNonQuery();
        }

        public void UpdateOnceTaskRowValue(OnceTaskCard pCard)
        {
            OleDbCommand myCommand = null;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("Update [{0}$] Set ", CommonData.ONCETASK_SHEETNAME);
            sb.AppendFormat("{0} = '{1}'", CommonData.ONCETASK_TYPE, pCard.ExecuteType);
            sb.AppendFormat(",{0} = '{1}'", CommonData.ONCETASK_CONTEXT, pCard.Context);
            sb.AppendFormat(",{0} = '{1}'", CommonData.ONCETASK_EXECUTEDATE, pCard.ExecuteDateTime);
            sb.AppendFormat(",{0} = '{1}'", CommonData.ONCETASK_DONEDATE, pCard.DoneDateTime);
            sb.AppendFormat(",{0} = '{1}'", CommonData.ONCETASK_NOTE, pCard.Note);
            if (pCard.DoneDateTime.HasValue)
            {
                sb.AppendFormat(",{0} = '{1}'", CommonData.ONCETASK_STATUS, CommonData.ONCETASK_STATUS_COMPLETE);
            }
            sb.AppendFormat("where ID='{0}'", pCard.CardID);
            myCommand = new OleDbCommand(sb.ToString(), this.conn);
            myCommand.ExecuteNonQuery();
        }

        public void InsertCycleTask(CycleTaskCard pCard)
        {
            OleDbCommand myCommand = null;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("Insert into [{0}$]({1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12})", CommonData.CYCLETASK_SHEETNAME
                , CommonData.ID, CommonData.CYCLETASK_TYPE, CommonData.CYCLETASK_FREQUENCY
               , CommonData.CYCLETASK_QUANTITY, CommonData.CYCLETASK_MONTH, CommonData.CYCLETASK_WEEKDAY, CommonData.CYCLETASK_DAY
               , CommonData.CYCLETASK_HOUR, CommonData.CYCLETASK_MINUTE, CommonData.CYCLETASK_CONTEXT, CommonData.CYCLETASK_NEXT, CommonData.CYCLETASK_IFONLYWORKDAY).AppendLine();
            sb.AppendFormat(" Values({0},'{1}','{2}',{3},{4},{5},{6},{7},{8},'{9}','{10}', '{11}')", Utility.GenerateID(), pCard.ExecuteType, pCard.Frequency.ToString()
                , pCard.Quantity, pCard.Month.ToString(), pCard.Weekday.ToString(), pCard.Day.ToString(), pCard.Hour.ToString(), pCard.Minute.ToString()
                , pCard.Context, pCard.ExecuteDateTime.ToString(), pCard.IfOnlyWorkday.ToString());

            myCommand = new OleDbCommand(sb.ToString(), this.conn);
            myCommand.ExecuteNonQuery();
        }

        public void InsertOnceTask(OnceTaskCard pCard)
        {
            OleDbCommand myCommand = null;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("Insert into [{0}$]", CommonData.ONCETASK_SHEETNAME);
            sb.AppendFormat("({0},[{1}],[{2}],[{3}],[{4}],[{5}])", CommonData.ID, CommonData.ONCETASK_CREATEDATE, CommonData.ONCETASK_TYPE, CommonData.ONCETASK_CONTEXT
                , CommonData.ONCETASK_EXECUTEDATE, CommonData.ONCETASK_NOTE).AppendLine();
            sb.AppendFormat("Values({0},'{1}','{2}','{3}','{4}','{5}')", Utility.GenerateID(), DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"), pCard.ExecuteType, pCard.Context
                , pCard.ExecuteDateTime, pCard.Note);
            myCommand = new OleDbCommand(sb.ToString(), this.conn);
            myCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// 插入To Do List
        /// </summary>
        /// <param name="pCard"></param>
        public void InsertToDoTask(TodoTaskCard pCard)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("Insert into [{0}$]", CommonData.TODOTASK_SHEETNAME);
            sb.AppendFormat("({0},{1},{2},{3},{4})", CommonData.ID, CommonData.TODOTASK_CONTEXT, CommonData.TODOTASK_STATUS, CommonData.TODOTASK_REMINDDATETIME, CommonData.TODOTASK_TASKTYPE).AppendLine();
            sb.AppendFormat("Values({0},'{1}','{2}','{3}','{4}')", pCard.ID, pCard.Context, pCard.Status, Utility.GetDateTimeString(DateTime.Now), pCard.TaskItemType.ToString());
            OleDbCommand myCommand = null;

            myCommand = new OleDbCommand(sb.ToString(), this.conn);
            myCommand.ExecuteNonQuery();
        }

        public void UpdateTodoTask(Int64 pID)
        {
            UpdateCellValue(CommonData.TODOTASK_SHEETNAME, CommonData.TODOTASK_STATUS, CommonData.TODOTASK_STATUS_COMPLETE, pID.ToString());
        }

        private void ExecuteNonQuery(string pSql)
        {
            this.conn.Open();
           
            this.conn.Close();
        }
    }
}
